﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace QLChoDiaOc.Process
{
    class SelectInfo
    {
        QLChoDiaOcEntities m_entities = new QLChoDiaOcEntities();

        #region Get All ID
        public List<string> SelectAllMaKH()
        {
            try
            {
                var list = from d in m_entities.KHACHHANGs
                           select d.MaKH;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaDiaOc()
        {
            try
            {
                var list = from d in m_entities.DIAOCs
                           select d.MaDiaOc;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaLoaiDiaOc()
        {
            try
            {
                var list = from d in m_entities.LOAIDIAOCs
                           select d.MaLoaiDiaOc;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaLoaiBang()
        {
            try
            {
                var list = from d in m_entities.LOAIBANGs
                           select d.MaLoaiBang;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaToBao()
        {
            try
            {
                var list = from d in m_entities.TOBAOs
                           select d.MaToBao;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }       

        public List<string> SelectAllMaPhieuDangKy()
        {
            try
            {
                var list = from d in m_entities.PHIEUDANGKies
                           select d.MaDangKy;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaQCBao()
        {
            try
            {
                var list = from d in m_entities.QC_BAO
                           select d.MaQCBao;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaQCBang()
        {
            try
            {
                var list = from d in m_entities.QC_BANG
                           select d.MaQCBang;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaQCToBuom()
        {
            try
            {
                var list = from d in m_entities.QC_TOBUOM
                           select d.MaQCToBuom;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaPhieuThu()
        {
            try
            {
                var list = from d in m_entities.PHIEUTHUs
                           select d.MaPhieuThu;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaNhanVien()
        {
            try
            {
                var list = from d in m_entities.NHANVIENs
                           select d.MaNhanVien;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllMaLoaiNhanVien()
        {
            try
            {
                var list = from d in m_entities.LOAINHANVIENs
                           select d.MaLoaiNV;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }
        #endregion

        public List<string> SelectAllTenLoaiDiaOc()
        {
            try
            {
                var list = from d in m_entities.LOAIDIAOCs
                           select d.TenLoaiDiaOc;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllTenLoaiBang()
        {
            try
            {
                var list = from d in m_entities.LOAIBANGs
                           select d.TenLoaiBang;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllTenToBao()
        {
            try
            {
                var list = from d in m_entities.TOBAOs
                           select d.TenToBao;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectAllTenLoaiNH()
        {
            try
            {
                var list = from d in m_entities.LOAINHANVIENs
                           select d.TenLoaiNV;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public string SelectMaLoaiDiaOc(string TenLoaiDiaOc)
        {
            try
            {
                var list = from d in m_entities.LOAIDIAOCs
                           where d.TenLoaiDiaOc == TenLoaiDiaOc
                           select d.MaLoaiDiaOc;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public KHACHHANG SelectKhachHang(string maKH)
        {
            try
            {
                var list = from d in m_entities.KHACHHANGs
                           where d.MaKH == maKH
                           select d;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public PHIEUTHU SelectPhieuThu(string MaPhieuThu)
        {
            try
            {
                var list = from d in m_entities.PHIEUTHUs
                           where d.MaPhieuThu == MaPhieuThu
                           select d;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public PHIEUDANGKY SelectPhieuDangKy(string MaDangKy)
        {
            try
            {
                var list = from d in m_entities.PHIEUDANGKies
                           where d.MaDangKy == MaDangKy
                           select d;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public DIAOC SelectDiaOc(string MaDiaOc)
        {
            try
            {
                var list = from d in m_entities.DIAOCs
                           where d.MaDiaOc == MaDiaOc
                           select d;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public QC_BAO SelectQCBao_MaDK(string MaDangKy)
        {
            try
            {
                var list = from d in m_entities.QC_BAO
                           where d.MaDangKy == MaDangKy
                           select d;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public int SelectCoChupHinhQCBao(string MaDangKy)
        {
            try
            {
                var list = from d in m_entities.QC_BAO
                           where d.MaDangKy == MaDangKy
                           select d.CoHinhAnh;
                return (int)list.First();
            }
            catch
            {
                return -1;
            }
        }

        public string SelectMaToBao(string TenToBao)
        {
            try
            {
                var list = from d in m_entities.TOBAOs
                           where d.TenToBao == TenToBao
                           select d.MaToBao;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public int SelectDonGiaToBao(string TenToBao)
        {
            try
            {
                var list = from d in m_entities.TOBAOs
                           where d.TenToBao == TenToBao
                           select d.DonGia;
                return (int)list.First();
            }
            catch
            {
                return -1;
            }
        }

        public string SelectMaLoaiBang(string TenLoaiBang)
        {
            try
            {
                var list = from d in m_entities.LOAIBANGs
                           where d.TenLoaiBang == TenLoaiBang
                           select d.MaLoaiBang;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public int SelectDonGiaLoaiBang(string TenLoaiBang)
        {
            try
            {
                var list = from d in m_entities.LOAIBANGs
                           where d.TenLoaiBang == TenLoaiBang
                           select d.DonGia;
                return (int)list.First();
            }
            catch
            {
                return -1;
            }
        }

        public string SelectTenLoaiDiaOc(string MaLoaiDiaOc)
        {
            try
            {
                var list = from d in m_entities.LOAIDIAOCs
                           where d.MaLoaiDiaOc == MaLoaiDiaOc
                           select d.TenLoaiDiaOc;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public string SelectTenLoaiBang(string MaLoaiBang)
        {
            try
            {
                var list = from d in m_entities.LOAIBANGs
                           where d.MaLoaiBang == MaLoaiBang
                           select d.TenLoaiBang;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public int SelectDonGiaBang(string MaLoaiBang)
        {
            try
            {
                var list = from d in m_entities.LOAIBANGs
                           where d.MaLoaiBang == MaLoaiBang
                           select d.DonGia;
                return (int)list.First();
            }
            catch
            {
                return -1;
            }
        }

        public string SelectTenToBao(string MaToBao)
        {
            try
            {
                var list = from d in m_entities.TOBAOs
                           where d.MaToBao == MaToBao
                           select d.TenToBao;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public string SelectTenLoaiNhanVien(string MaLoaiNV)
        {
            try
            {
                var list = from d in m_entities.LOAINHANVIENs
                           where d.MaLoaiNV == MaLoaiNV
                           select d.TenLoaiNV;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public string SelectMaLoaiNhanVien(string TenLoaiNV)
        {
            try
            {
                var list = from d in m_entities.LOAINHANVIENs
                           where d.TenLoaiNV == TenLoaiNV
                           select d.MaLoaiNV;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        #region Select Tham so
        public float SelectThamSo(string TenThamSo)
        {
            try
            {
                var list = from d in m_entities.THAMSOes
                           where d.TenThamSo == TenThamSo
                           select d.GiaTri;
                return (float)list.First();
            }
            catch
            {
                return -1;
            }
        }

        public float SelectThamSo_SoTuToiDa()
        {
            try
            {
                var list = from d in m_entities.THAMSOes
                           where d.TenThamSo == "SoTuToiDa"
                           select d.GiaTri;
                return (float)list.First();
            }
            catch
            {
                return -1;
            }
        }

        public float SelectThamSo_SoToBuomToiThieu()
        {
            try
            {
                var list = from d in m_entities.THAMSOes
                           where d.TenThamSo == "SoToBuomToiThieu"
                           select d.GiaTri;
                return (float)list.First();
            }
            catch
            {
                return -1;
            }
        }

        public float SelectThamSo_SoThangCoGiaTri()
        {
            try
            {
                var list = from d in m_entities.THAMSOes
                           where d.TenThamSo == "SoThangCoGiaTri"
                           select d.GiaTri;
                return (float)list.First();
            }
            catch
            {
                return -1;
            }
        }

        public float SelectThamSo_SoNgayHenChupAnhToiDa()
        {
            try
            {
                var list = from d in m_entities.THAMSOes
                           where d.TenThamSo == "SoNgayHenChupAnhToiDa"
                           select d.GiaTri;
                return (float)list.First();
            }
            catch
            {
                return -1;
            }
        }

        public float SelectThamSo_TiLePhieuThuKeTiep()
        {
            try
            {
                var list = from d in m_entities.THAMSOes
                           where d.TenThamSo == "TiLePhieuThuKeTiep"
                           select d.GiaTri;
                return (float)list.First();
            }
            catch
            {
                return -1;
            }
        }
        #endregion

        public string SelectMatKhau(string TenDangNhap)
        {
            try
            {
                var list = from d in m_entities.DANGNHAPs
                           where d.TenDangNhap == TenDangNhap
                           select d.MatKhau;
                return list.First();
            }
            catch
            {
                return "-1";
            }
        }

        public List<string> SelectBCQCBangTheoThang(int Thang, int Nam)
        {
            try
            {                
                var list = from dangky in m_entities.PHIEUDANGKies
                           from qcBang in m_entities.QC_BANG
                           where dangky.MaDangKy == qcBang.MaDangKy && 
                                   dangky.NgayDangKy.Value.Month == Thang && 
                                   dangky.NgayDangKy.Value.Year == Nam
                           select qcBang.MaQCBang;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectBCQCBaoTheoThang(int Thang, int Nam)
        {
            try
            {
                var list = from dangky in m_entities.PHIEUDANGKies
                           from qcBao in m_entities.QC_BAO
                           where dangky.MaDangKy == qcBao.MaDangKy &&
                                   dangky.NgayDangKy.Value.Month == Thang &&
                                   dangky.NgayDangKy.Value.Year == Nam
                           select qcBao.MaQCBao;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public List<string> SelectBCQCToBuomTheoThang(int Thang, int Nam)
        {
            try
            {
                var list = from dangky in m_entities.PHIEUDANGKies
                           from qcToBuom in m_entities.QC_TOBUOM
                           where dangky.MaDangKy == qcToBuom.MaDangKy &&
                                   dangky.NgayDangKy.Value.Month == Thang &&
                                   dangky.NgayDangKy.Value.Year == Nam
                           select qcToBuom.MaQCToBuom;
                return list.ToList();
            }
            catch
            {
                return null;
            }
        }

        public string SelectMaLoaiNV(string MaNhanVien)
        {
            try
            {
                var list = from nhanvien in m_entities.NHANVIENs
                           where nhanvien.MaNhanVien == MaNhanVien
                           select nhanvien.MaLoaiNV;
                return list.First();
            }
            catch
            {
                return null;
            }
        }

        public string SelectHoTenNhanVien(string MaNhanVien)
        {
            try
            {
                var list = from nhanvien in m_entities.NHANVIENs
                           where nhanvien.MaNhanVien == MaNhanVien
                           select nhanvien.HoTenNV;
                return list.First();
            }
            catch
            {
                return null;
            }
        }
    }
}
